'''商品推荐算法（计算商品相似度、生成推荐）'''
# data_loader.py

import pymysql
import pandas as pd
from config import Config

class DataLoader:
    def __init__(self):
        """初始化数据库连接"""
        self.connection = None
        try:
            self.connection = pymysql.connect(
                host=Config.MYSQL_HOST,
                user=Config.MYSQL_USER,
                password=Config.MYSQL_PASSWORD,
                db=Config.MYSQL_DB,
                charset='utf8mb4'
            )
        except pymysql.MySQLError as e:
            print(f"Error connecting to MySQL Database: {e}")
            raise

    def get_user_purchase_data(self):
        """获取用户购买记录"""
        try:
            query = "SELECT username, goods_sn FROM litemall_records"
            # 使用 pandas 直接执行查询并返回 DataFrame
            df = pd.read_sql(query, self.connection)
            return df
        except Exception as e:
            print(f"Error fetching data: {e}")
            return None

    def get_goods_info_by_goods_sn(self,goods_sn_list):
        ''''根据商品编号进行商品数据的查找'''
        try:
            #使用IN 子句查询多个goods informations
            query = f"""
            SELECT goods_sn,name,brief,pic_url,retail_price,counter_price
            FROM litemall_goods
            WHERE goods_sn IN ({','.join(['%s']*len(goods_sn_list))})
            """
            # 执行查询，并返回查询结果为 DataFrame
            df = pd.read_sql(query,self.connection,params=goods_sn_list)
            return df
        except Exception as e:
            print(f"Error fetching data: {e}")
            return None

    def close_connection(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()